package db;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import javax.sql.rowset.serial.SerialBlob;
import java.io.ByteArrayOutputStream;
import java.io.ObjectOutputStream;
import java.sql.Connection;
import java.util.List;

/**
 * MySQL数据库操作方法封装
 * dbUtils通过setObject的方式传递参数，可有效防止sql注入
 */
public class DBQuery {

    private final static QueryRunner queryRunner = new QueryRunner();

    /**
     * 查询单个Java对象
     */
    public static <T> T read_bean(Class<T> beanClass, String sql, Object... params) {
        Connection connection = null;
        try {
            connection = DruidDBPool.getConn();
            //看源码，query会关闭PreparedStatement和ResultSet，但不会关闭Connection连接，因此需要手动关一下
            return queryRunner.query(connection,sql, new BeanHandler<T>(beanClass),params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DruidDBPool.close(connection);
        }
        return null;
    }

    /**
     * 查询结果为基础数据类型的封装类的对象
     */
    public static <T> T read_scalar(String sql, Object... params) {
        Connection connection = null;
        try {
            connection = DruidDBPool.getConn();
            return queryRunner.query(connection,sql, new ScalarHandler<T>(),params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DruidDBPool.close(connection);
        }
        return null;
    }

    public static <T> List<T> query_list(Class<T> beanClass, String sql, Object... params) {
        Connection connection = null;
        try {
            connection = DruidDBPool.getConn();
            return queryRunner.query(connection,sql, new BeanListHandler<T>(beanClass),params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DruidDBPool.close(connection);
        }
        return null;
    }

    /**
     * 更新/删除数据，并返回影响的行数
     */
    public static long update(String sql, Object... params) {
        Connection connection = null;
        try {
            connection = DruidDBPool.getConn();
            return queryRunner.update(connection, sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DruidDBPool.close(connection);
        }
        return -1;
    }

    /**
     * 插入数据，并返回自动生成的主键id
     */
    public static long insert(String sql, Object... params) {
        Connection connection = null;
        try {
            connection = DruidDBPool.getConn();
            return queryRunner.insert(connection, sql, new ScalarHandler<>(), params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DruidDBPool.close(connection);
        }
        return -1;
    }

    public static long updateBlob(String sql, Object blobObject) {
        Connection connection = null;
        try {
            //将对象转换为字节流
            ByteArrayOutputStream byteStream = new ByteArrayOutputStream();
            ObjectOutputStream objStream = new ObjectOutputStream(byteStream);
            objStream.writeObject(blobObject);
            byte[] byteArray = byteStream.toByteArray();
            SerialBlob blob = new SerialBlob(byteArray);

            connection = DruidDBPool.getConn();
            return queryRunner.update(connection, sql, blob);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DruidDBPool.close(connection);
        }
        return -1;
    }
}
